
package com.feib.soeasy.dao.jdbc;

import java.sql.ResultSet;
import java.sql.SQLException;
import java.text.SimpleDateFormat;
import java.util.ArrayList;
import java.util.Date;
import java.util.List;
import java.util.Map;

import javax.annotation.Resource;
import javax.sql.DataSource;


import org.apache.commons.lang.StringUtils;
import org.slf4j.Logger;
import org.slf4j.LoggerFactory;
import org.springframework.jdbc.core.ColumnMapRowMapper;
import org.springframework.jdbc.core.ResultSetExtractor;
import org.springframework.jdbc.core.RowMapper;
import org.springframework.jdbc.core.support.JdbcDaoSupport;
import org.springframework.stereotype.Repository;
import org.springframework.util.Assert;

import com.feib.soeasy.dao.Soez6001Dao;
import com.feib.soeasy.model.Group;
import com.feib.soeasy.pagination.PaginatedList;


/**
 * 
 * * @author Jeff Jeff Gu
 *
 */
@Repository("soez6001Dao")
public class Soez6001JdbcDao extends JdbcDaoSupport implements Soez6001Dao {
	protected Logger logger = LoggerFactory.getLogger(this.getClass());

	@Resource(name = "dataSource")
	public void setMyDataSource(DataSource dataSource) {
		super.setDataSource(dataSource); 
	}





	/** 
	 * 放大鏡明細資料來源
	 *  
	 */
	public PaginatedList<Map<String, Object>> queryTotalGroupFeedbackLog(Group group ,String[] channels , Date[] dateFroms, Date[] dateTos , PaginatedList<Map<String, Object>> paginationConfig)
	{

		List<Object> args = new ArrayList<Object>();
		String sql=queryTotalGroupFeedbackLogSql(group, channels, dateFroms, dateTos, args,true);

		logger.debug("queryTotalGroupFeedbackLog SQL="+sql+"; args="+args);

		RowMapper<Map<String, Object>> rowMapper = new ColumnMapRowMapper();
		PaginatedList<Map<String, Object>> result = new PaginatedList<Map<String,Object>>();
		result.setPageSize(paginationConfig.getPageSize());
		result.setPageNumber(paginationConfig.getPageNumber());
		result.setSortCriterion(paginationConfig.getSortCriterion());
		result.setSortDirection(paginationConfig.getSortDirection());


		RowMapperResultSetExtractor<Map<String, Object>> extracotr = new RowMapperResultSetExtractor<Map<String, Object>>(rowMapper, result);
		this.getJdbcTemplate().query(sql.toString(), args.toArray(), extracotr);
		/* 0644*/
		if(extracotr.getPaginatedList().isLastPage()){
			List<Object> argSum = new ArrayList<Object>();
			String sqlSum=querySumTotalGroupFeedbackLogSql(group, channels, dateFroms, dateTos, argSum);
			Map<String,Object> SumTotal=this.getJdbcTemplate().queryForMap(sqlSum.toString(), argSum.toArray());
			extracotr.getPaginatedList().setTotalObject(SumTotal);
		}
		return extracotr.getPaginatedList();

	}


	public String queryTotalGroupFeedbackLogSql(Group group ,String[] channels , Date[] dateFroms, Date[] dateTos , List<Object> args, boolean order){

		StringBuffer sql = new StringBuffer();
		int count=0;
		int lastIndex=0;
		for(int i=0;i<channels.length;i++){
			String channel = channels[i];
			if(!"null".equalsIgnoreCase(channel) && StringUtils.isNotBlank(channel) ){
				count++;
				lastIndex=i;
			}
		}
		for(int i=0;i<channels.length;i++)
		{
			String channel = channels[i];
			Date dateFrom = dateFroms[i];
			Date dateTo = dateTos[i];
			//撥款日期 繳費期限 繳費日期 客戶名稱 繳費帳號 繳費金額 代收手續費 實撥金額
			if(!"null".equalsIgnoreCase(channel) && StringUtils.isNotBlank(channel)){
				sql.append("select t.TRANSFER_TIME as transferTime ,t.channel,t.channel_code, ").append("\r\n");

				if(channel != null && channel.equals("H")){
					sql.append("(select top 1 Payment.PAY_DUE from Payment payment where payment.VIRTUAL_ACCOUNT= userno  ) as dueday  ").append("\r\n");
				}else if(channel != null && !channel.equals("H")){
					sql.append("CONVERT(datetime,(CONVERT(varchar(4),(substring(barcdoe1,1,2)+2011))+substring(barcdoe1,3,2)+substring(barcdoe1,5,2))) as dueday  ").append("\r\n");

				}
				sql.append(",F.aday, F.txamt ").append("\r\n");
				sql.append(",P.PAYER_NAME as payerName ").append("\r\n");
				sql.append(", case substring(userno,len(userno)-1,2) when '00' then substring(userno,1,14) else userno end as userno ").append("\r\n");			
				sql.append(",POST_LEVEL1 as  post_1,POST_LEVEL2  as post_2,POST_LEVEL3  as post_3 ").append("\r\n");
				sql.append(", (case   F.channel ").append("\r\n");
				sql.append("  WHEN 'POST' ").append("\r\n");
				sql.append("  THEN ").append("\r\n");
				sql.append("  case ").append("\r\n");
				sql.append("  	WHEN f. txamt<=100  THEN  ISNULL(a.POST_LEVEL1,0)  ").append("\r\n");
				sql.append("  	WHEN f. txamt<=1000 THEN  ISNULL( a.POST_LEVEL2 ,0) ").append("\r\n");
				sql.append("  	WHEN  f.txamt >1000  THEN  ISNULL(a. POST_LEVEL3 ,0) ").append("\r\n");
				sql.append("  ELSE 0 ").append("\r\n");
				sql.append("  END ").append("\r\n");
				sql.append("  ELSE ").append("\r\n");
				sql.append("  ISNULL(GROUP_AMOUNT,0) ").append("\r\n");
				sql.append("  END ").append("\r\n");
				sql.append("  )  as fee ").append("\r\n");

				sql.append(" from   TRANSFER_FEEDBACK t ").append("\r\n");
				sql.append(" inner join app_group  a ").append("\r\n");				
				sql.append(" on t.group_no=a.group_no").append("\r\n");	

				sql.append(" inner join  real_account r ").append("\r\n");		
				sql.append(" on r.group_pk=a.group_pk ").append("\r\n");

				sql.append(" inner join feedback_log f ").append("\r\n");
				sql.append(" on r.virtual_account_no = substring(f.userno, 1, len(r.virtual_account_no))").append("\r\n");		
				sql.append(" and  feedback_type = 2").append("\r\n");
				sql.append(" and  fbcode=1").append("\r\n");
				sql.append("and t.channel_code = ? ");					
				args.add(channel);
				//TODO
				sql.append(" and convert(varchar(8),f.fbtime,112) = convert(varchar(8),t.fbtime,112) ").append("\r\n");
				sql.append(" and t.CHANNEL=f.CHANNEL").append("\r\n");

				sql.append(" left join COMMISSION_CODE_TABLE c ").append("\r\n");			
				sql.append(" on  COMMISSION_CODE=substring(barcdoe1,len(barcdoe1)-2,3)  ").append("\r\n");				

				sql.append(" inner join  Payment p").append("\r\n");
				sql.append(" on  p.VIRTUAL_ACCOUNT=substring(f.userno,1,14)").append("\r\n");				

				sql.append(" where 1=1 ").append("\r\n");		

				/* 沒有傳入group就抓全部 */
				if(null != group.getGroupNo()){
					sql.append(" and a.group_no = ? ").append("\r\n");
					args.add(group.getGroupNo());
				}


				sql.append("and convert(varchar(8),T.transfer_Time,112) between  ?  and ? ").append("\r\n");

				SimpleDateFormat dateFormat = new SimpleDateFormat("yyyyMMdd");
				args.add(dateFormat.format(dateFrom));
				args.add(dateFormat.format(dateTo));
				/* 串各群組 畫面可能會跳號選取 通路*/
				/* 不跳號選取通路時  INDEX比實際選取個數小的狀況下要加union 表示後面還有要串的資料   */
				if(i<= count && i<lastIndex){
					sql.append(" union all ");
				}
				/* 跳號選取通路時  INDEX比實際選取個數大且不是最後一筆的狀況下要加union 表示後面還有要串的資料   */
				if(i>count && i<lastIndex){
					sql.append(" union all ");
				}
			}
		}

		if(order){
			sql.append("order by T.transfer_Time ,t.channel_code, userno ");
		}

		logger.debug("queryTotalGroupFeedbackLogSql SQL="+sql+"; args="+args);


		return sql.toString();
	};

	/** 
	 * 總計放大鏡明細資料用
	 * 顯示在最下方,最後一頁再顯示
	 * 
	 */
	public String querySumTotalGroupFeedbackLogSql(Group group ,String[] channels , Date[] dateFroms, Date[] dateTos , List<Object> args){	
		StringBuffer sql = new StringBuffer();
		sql.append("select  count(*) as totalCount ,sum(txamt) as totalAmount ,sum(FEE) as totalFee  from ( ");
		sql.append(queryTotalGroupFeedbackLogSql(group, channels, dateFroms, dateTos, args,false));
		sql.append(" )  a  where 1=1 ");		
		logger.debug("querySumTotalGroupFeedbackLogSql SQL="+sql+"; args="+args);

		/*釋放資源 */		
		return sql.toString();
	};



	class RowMapperResultSetExtractor<T> implements ResultSetExtractor<List<T>>
	{
		private RowMapper<T> rowMapper;
		private PaginatedList<T> paginatedList;

		public RowMapperResultSetExtractor(RowMapper<T> rowMapper, PaginatedList<T> paginatedList) {
			Assert.notNull(rowMapper, "RowMapper is required");
			Assert.notNull(paginatedList, "PaginatedList is required");
			this.rowMapper = rowMapper;
			this.paginatedList = paginatedList;
		}


		public List<T> extractData(ResultSet rs) throws SQLException {
			List<T> results = new ArrayList<T>();

			int maxResults = paginatedList.getPageSize(); //the maximum number of results
			int firstResult = paginatedList.getFirstIndexForCurrentPage();  //the first result to retrieve, numbered from 0
			int lastResult = firstResult + maxResults -1;  //the first result to retrieve, numbered from 0

			int rowNum = 0;
			while (rs.next()) {

				if (maxResults==-1)
					results.add(this.rowMapper.mapRow(rs, rowNum++));
				else if (firstResult <= rowNum && lastResult >= rowNum)
					results.add(this.rowMapper.mapRow(rs, rowNum++));
				else
					rowNum++;
			}

			paginatedList.setFullListSize(rowNum);
			paginatedList.setPaginatedList(results);
			return results;

		}



		/**
		 * @return the paginatedList
		 */
		public PaginatedList<T> getPaginatedList() {
			return paginatedList;
		}
	}



	/**
	 * TODO 20130221
	 * 取得各通路撥款總計
	 * @author Jeff  Gu 		
	 * @param group
	 * @param channels
	 * @param dateFrom
	 * @param dateTo
	 * @param gueryGroupType 群組類別
	 * @param transfered  是否撥款
	 * @param paginationConfig
	 * @return
	 */
	public PaginatedList<Map<String, Object>> queryTransferLogByGroup(Group group, String channels[],
			Date[] dateFroms, Date[] dateTos,List<String> gueryGroupType,String transfered,PaginatedList<Map<String, Object>> paginationConfig)
			{	
		List<Object> args = new ArrayList<Object>();

		String sql=getQueryGroupTransferLogSql(group, channels, dateFroms, dateTos, gueryGroupType, transfered, args, true);

		logger.debug("6001 queryTransferLog sql={};  args={}", sql, args);
		RowMapper<Map<String, Object>> rowMapper = new ColumnMapRowMapper();


		PaginatedList<Map<String, Object>> result = new PaginatedList<Map<String,Object>>();
		/* 取消分頁 ,設定  PageSize=-1*/
		if(-1==paginationConfig.getPageSize()){
			result.setPaginatedList(this.getJdbcTemplate().queryForList(sql.toString(), args.toArray()));
			return result;
		}
		result.setPageSize(paginationConfig.getPageSize());
		result.setPageNumber(paginationConfig.getPageNumber());
		result.setSortCriterion(paginationConfig.getSortCriterion());
		result.setSortDirection(paginationConfig.getSortDirection());

		RowMapperResultSetExtractor<Map<String, Object>> extracotr = new RowMapperResultSetExtractor<Map<String, Object>>(rowMapper, result);
		this.getJdbcTemplate().query(sql.toString(), args.toArray(), extracotr);

		return extracotr.getPaginatedList();

			}


	/**
	 * 6001查詢功能 統計畫面資料來源
	 * @author Jeff 
	 * TODO 20130219
	 */
	public String getQueryGroupTransferLogSql(Group group, String channels[],
			Date[] qryDateFroms, Date[] qryDateTos,List<String> queryGroupType,String transfered,List<Object> args, boolean order)
	{
		StringBuffer sql = new StringBuffer();
		args.clear();

		int count=0;
		int lastIndex=0;
		for(int i=0;i<channels.length;i++){
			String channel = channels[i];
			if(!"null".equalsIgnoreCase(channel) && StringUtils.isNotBlank(channel) ){
				count++;
				lastIndex=i;
			}
		}

		sql.append("select ").append("\r\n");

		sql.append(" transferTime   ,groupno  ,accountno,groupname ").append("\r\n");
		sql.append(",sum(case   channel_code   WHEN 'H'   THEN 0  WHEN 'D'   THEN  CHANNEL_COUNT WHEN 'E'   THEN  0 WHEN 'F'   THEN 0 WHEN 'G'   THEN  0 end) as   DCOUNT ").append("\r\n");	
		sql.append(", sum( case   channel_code   WHEN 'H'   THEN 0  WHEN 'D'   THEN    0 WHEN 'E'   THEN  CHANNEL_COUNT WHEN 'F'   THEN 0  WHEN 'G'   THEN  0 end ) as   ECOUNT ").append("\r\n");	
		sql.append(", sum( case   channel_code   WHEN 'H'   THEN 0  WHEN 'D'   THEN    0 WHEN 'E'   THEN 0  WHEN 'F'   THEN CHANNEL_COUNT  WHEN 'G'   THEN  0 end ) as   FCOUNT ").append("\r\n");	
		sql.append(", sum( case   channel_code   WHEN 'H'   THEN 0  WHEN 'D'   THEN    0 WHEN 'E'   THEN 0  WHEN 'F'   THEN 0   WHEN 'G'   THEN  CHANNEL_COUNT end ) as  GCOUNT ").append("\r\n");	
		sql.append(", sum( case   channel_code   WHEN 'H'   THEN  CHANNEL_COUNT  WHEN 'D'   THEN    0 WHEN 'E'   THEN 0  WHEN 'F'   THEN 0   WHEN 'G'   THEN  0  end ) as  HCOUNT ").append("\r\n");	

		sql.append(", sum(CHANNEL_COUNT) AS  TOTALCOUNT").append("\r\n");	

		sql.append(", sum(COMMISSION_AMOUNT)  AS  COMMISSIONAMOUNT").append("\r\n");	

		sql.append(",sum(case   channel_code   WHEN 'H'   THEN 0  WHEN 'D'   THEN  (txamt-COMMISSION_AMOUNT ) WHEN 'E'   THEN  0 WHEN 'F'   THEN 0 WHEN 'G'   THEN  0 end) as   DAMOUNT ").append("\r\n");	
		sql.append(", sum( case   channel_code   WHEN 'H'   THEN 0  WHEN 'D'   THEN    0 WHEN 'E'   THEN  (txamt-COMMISSION_AMOUNT ) WHEN 'F'   THEN 0  WHEN 'G'   THEN  0 end ) as   EAMOUNT ").append("\r\n");	
		sql.append(", sum( case   channel_code   WHEN 'H'   THEN 0  WHEN 'D'   THEN    0 WHEN 'E'   THEN 0  WHEN 'F'   THEN (txamt-COMMISSION_AMOUNT )  WHEN 'G'   THEN  0 end ) as   FAMOUNT ").append("\r\n");	
		sql.append(", sum( case   channel_code   WHEN 'H'   THEN 0  WHEN 'D'   THEN    0 WHEN 'E'   THEN 0  WHEN 'F'   THEN 0   WHEN 'G'   THEN  (txamt-COMMISSION_AMOUNT ) end ) as  GAMOUNT ").append("\r\n");	
		sql.append(", sum( case   channel_code   WHEN 'H'   THEN  (txamt-COMMISSION_AMOUNT )  WHEN 'D'   THEN    0 WHEN 'E'   THEN 0  WHEN 'F'   THEN 0   WHEN 'G'   THEN  0  end ) as  HAMOUNT").append("\r\n");	

		sql.append(", sum(txamt ) AS TOTALAMOUNT").append("\r\n");	

		sql.append(", sum(txamt-COMMISSION_AMOUNT ) AS REALAMOUNT").append("\r\n");	
		sql.append("  from( ").append("\r\n");
		for(int i=0;i<channels.length;i++)
		{
			String channel = channels[i];
			Date dateFrom = qryDateFroms[i];
			Date dateTo = qryDateTos[i];


			sql.append("  select   ").append("\r\n");	
			sql.append("  t.transfer_Time transfertime").append("\r\n");	
			sql.append("  ,t.group_no groupno ").append("\r\n");	
			sql.append("  ,t.account_no accountno").append("\r\n");	
			sql.append("  ,a.group_name groupname").append("\r\n");	

			sql.append(",  channel_code   ").append("\r\n");	

			sql.append(", t.CHANNEL_COUNT").append("\r\n");	

			sql.append(", t. COMMISSION_AMOUNT").append("\r\n");	

			sql.append(", txamt ").append("\r\n");	


			sql.append(" from TRANSFER_FEEDBACK t ").append("\r\n");	
			sql.append(" inner join app_group  a ").append("\r\n");				
			sql.append(" on t.group_no=a.group_no  ").append("\r\n");	

			/* 沒有傳入group就抓全部 */
			if(null != group.getGroupNo()){
				sql.append(" and t.group_no = ? ").append("\r\n");	
				args.add(group.getGroupNo());
			}


			StringBuffer secs = new StringBuffer();
			for (String x: queryGroupType)
			{
				secs.append(" ?").append(",");
				args.add(x);
			}
			sql.append(" AND t.Group_Type IN (").append(secs.substring(0, secs.length()-1)).append(")").append("\r\n");

			sql.append(" AND t.transfer_code = ? ").append("\r\n");	
			args.add(transfered);

			sql.append(" AND CHANNEL_CODE = ? ").append("\r\n");	
			args.add(channel);
			sql.append(" and convert(varchar(8),T.transfer_Time,112) between convert(varchar(8),?,112) and convert(varchar(8),?,112)  ").append("\r\n");	
			args.add(dateFrom);
			args.add(dateTo);
			sql.append("group by   t.transfer_Time,t.account_no ,t.group_no,group_name   ,t.CHANNEL_COUNT,t.COMMISSION_AMOUNT,txamt,channel_code ").append("\r\n");	


			/* 串各群組 畫面可能會跳號選取 通路*/
			/* 不跳號選取通路時  INDEX比實際選取個數小的狀況下要加union 表示後面還有要串的資料   */
			if(i<= count && i<lastIndex){
				sql.append(" union all ");
			}
			/* 跳號選取通路時  INDEX比實際選取個數大且不是最後一筆的狀況下要加union 表示後面還有要串的資料   */
			if(i>count && i<lastIndex){
				sql.append(" union all ");
			}
		}

		sql.append(") a").append("\r\n");	

		sql.append("group by   transferTime,accountno ,groupno,groupname  ,channel_code  ").append("\r\n");	


		if(order){
			sql.append("order by  transferTime,accountno ,groupno,groupname  ");
		}



		logger.debug("getQueryGroupTransferLogSql SQL="+sql+"; args="+args);
		return sql.toString();
	}




	/**
	 * TODO 20130222
	 * 取得各通路未撥款資料總計
	 * @author Jeff  Gu 		
	 * @param group
	 * @param channels
	 * @param dateFrom
	 * @param dateTo
	 * @param gueryGroupType 群組類別
	 * @param transfered  是否撥款
	 * @param paginationConfig
	 * @return
	 */
	public PaginatedList<Map<String, Object>> queryNonTransferLogByGroup(Group group, String channels[],
			Date[] dateFroms, Date[] dateTos,List<String> gueryGroupType,String transfered,PaginatedList<Map<String, Object>> paginationConfig)
			{	
		List<Object> args = new ArrayList<Object>();

		String sql=getQueryNonTransferLogSql(group, channels, dateFroms, dateTos, gueryGroupType, transfered, args, true);

		logger.debug("6001 queryStroeTransferLog sql={};  args={}", sql, args);
		RowMapper<Map<String, Object>> rowMapper = new ColumnMapRowMapper();


		PaginatedList<Map<String, Object>> result = new PaginatedList<Map<String,Object>>();
		/* 取消分頁 ,設定  PageSize=-1*/
		if(-1==paginationConfig.getPageSize()){
			result.setPaginatedList(this.getJdbcTemplate().queryForList(sql.toString(), args.toArray()));
			return result;
		}
		result.setPageSize(paginationConfig.getPageSize());
		result.setPageNumber(paginationConfig.getPageNumber());
		result.setSortCriterion(paginationConfig.getSortCriterion());
		result.setSortDirection(paginationConfig.getSortDirection());

		RowMapperResultSetExtractor<Map<String, Object>> extracotr = new RowMapperResultSetExtractor<Map<String, Object>>(rowMapper, result);
		this.getJdbcTemplate().query(sql.toString(), args.toArray(), extracotr);

		return extracotr.getPaginatedList();

			}


	 /* 6001查詢功能 統計畫面未撥款資料來源
	 * @author Jeff 
	 * TODO 20130222
	 */
	public String getQueryNonTransferLogSql(Group group, String channels[],
			Date[] qryDateFroms, Date[] qryDateTos,List<String> queryGroupType,String transfered,List<Object> args, boolean order)
	{
		StringBuffer sql = new StringBuffer();
		args.clear();

		int count=0;
		int lastIndex=0;
		for(int i=0;i<channels.length;i++){
			String channel = channels[i];
			if(!"null".equalsIgnoreCase(channel) && StringUtils.isNotBlank(channel) ){
				count++;
				lastIndex=i;
			}
		}

		sql.append("select ").append("\r\n");

		sql.append(" groupno  ,accountno,groupname ").append("\r\n");
		sql.append(",sum(case   channel_code   WHEN 'H'   THEN 0  WHEN 'D'   THEN  CHANNEL_COUNT WHEN 'E'   THEN  0 WHEN 'F'   THEN 0 WHEN 'G'   THEN  0 end) as   DCOUNT ").append("\r\n");	
		sql.append(", sum( case   channel_code   WHEN 'H'   THEN 0  WHEN 'D'   THEN    0 WHEN 'E'   THEN  CHANNEL_COUNT WHEN 'F'   THEN 0  WHEN 'G'   THEN  0 end ) as   ECOUNT ").append("\r\n");	
		sql.append(", sum( case   channel_code   WHEN 'H'   THEN 0  WHEN 'D'   THEN    0 WHEN 'E'   THEN 0  WHEN 'F'   THEN CHANNEL_COUNT  WHEN 'G'   THEN  0 end ) as   FCOUNT ").append("\r\n");	
		sql.append(", sum( case   channel_code   WHEN 'H'   THEN 0  WHEN 'D'   THEN    0 WHEN 'E'   THEN 0  WHEN 'F'   THEN 0   WHEN 'G'   THEN  CHANNEL_COUNT end ) as  GCOUNT ").append("\r\n");	
		sql.append(", sum( case   channel_code   WHEN 'H'   THEN  CHANNEL_COUNT  WHEN 'D'   THEN    0 WHEN 'E'   THEN 0  WHEN 'F'   THEN 0   WHEN 'G'   THEN  0  end ) as  HCOUNT ").append("\r\n");	

		sql.append(", sum(CHANNEL_COUNT) AS  TOTALCOUNT").append("\r\n");	

		sql.append(", sum(COMMISSION_AMOUNT)  AS  COMMISSIONAMOUNT").append("\r\n");	

		sql.append(",sum(case   channel_code   WHEN 'H'   THEN 0  WHEN 'D'   THEN  (txamt-COMMISSION_AMOUNT ) WHEN 'E'   THEN  0 WHEN 'F'   THEN 0 WHEN 'G'   THEN  0 end) as   DAMOUNT ").append("\r\n");	
		sql.append(", sum( case   channel_code   WHEN 'H'   THEN 0  WHEN 'D'   THEN    0 WHEN 'E'   THEN  (txamt-COMMISSION_AMOUNT ) WHEN 'F'   THEN 0  WHEN 'G'   THEN  0 end ) as   EAMOUNT ").append("\r\n");	
		sql.append(", sum( case   channel_code   WHEN 'H'   THEN 0  WHEN 'D'   THEN    0 WHEN 'E'   THEN 0  WHEN 'F'   THEN (txamt-COMMISSION_AMOUNT )  WHEN 'G'   THEN  0 end ) as   FAMOUNT ").append("\r\n");	
		sql.append(", sum( case   channel_code   WHEN 'H'   THEN 0  WHEN 'D'   THEN    0 WHEN 'E'   THEN 0  WHEN 'F'   THEN 0   WHEN 'G'   THEN  (txamt-COMMISSION_AMOUNT ) end ) as  GAMOUNT ").append("\r\n");	
		sql.append(", sum( case   channel_code   WHEN 'H'   THEN  (txamt-COMMISSION_AMOUNT )  WHEN 'D'   THEN    0 WHEN 'E'   THEN 0  WHEN 'F'   THEN 0   WHEN 'G'   THEN  0  end ) as  HAMOUNT").append("\r\n");	

		sql.append(", sum(txamt ) AS TOTALAMOUNT").append("\r\n");	

		sql.append(", sum(txamt-COMMISSION_AMOUNT ) AS REALAMOUNT").append("\r\n");	
		sql.append("  from( ").append("\r\n");
		for(int i=0;i<channels.length;i++)
		{
			String channel = channels[i];
			Date dateFrom = qryDateFroms[i];
			Date dateTo = qryDateTos[i];


			sql.append("  select   ").append("\r\n");	
			sql.append("  ,t.group_no groupno ").append("\r\n");	
			sql.append("  ,t.account_no accountno").append("\r\n");	
			sql.append("  ,a.group_name groupname").append("\r\n");	

			sql.append(",  channel_code   ").append("\r\n");	

			sql.append(", sum(t.CHANNEL_COUNT)  CHANNEL_COUNT ").append("\r\n");	

			sql.append(", sum(t. COMMISSION_AMOUNT)  COMMISSION_AMOUNT ").append("\r\n");	

			sql.append(", sum(txamt)  txamt ").append("\r\n");	


			sql.append(" from TRANSFER_FEEDBACK t ").append("\r\n");	
			sql.append(" inner join app_group  a ").append("\r\n");				
			sql.append(" on t.group_no=a.group_no  ").append("\r\n");	

			/* 沒有傳入group就抓全部 */
			if(null != group.getGroupNo()){
				sql.append(" and t.group_no = ? ").append("\r\n");	
				args.add(group.getGroupNo());
			}


			StringBuffer secs = new StringBuffer();
			for (String x: queryGroupType)
			{
				secs.append(" ?").append(",");
				args.add(x);
			}
			sql.append(" AND t.Group_Type IN (").append(secs.substring(0, secs.length()-1)).append(")").append("\r\n");

			sql.append(" AND t.transfer_code = ? ").append("\r\n");	
			args.add(transfered);

			sql.append(" AND CHANNEL_CODE = ? ").append("\r\n");	
			args.add(channel);
			sql.append(" and convert(varchar(8),T.fbtime,112) between convert(varchar(8),?,112) and convert(varchar(8),?,112)  ").append("\r\n");	
			args.add(dateFrom);
			args.add(dateTo);
			sql.append("group by   t.account_no ,t.group_no,group_name,channel_code ").append("\r\n");	


			/* 串各群組 畫面可能會跳號選取 通路*/
			/* 不跳號選取通路時  INDEX比實際選取個數小的狀況下要加union 表示後面還有要串的資料   */
			if(i<= count && i<lastIndex){
				sql.append(" union all ");
			}
			/* 跳號選取通路時  INDEX比實際選取個數大且不是最後一筆的狀況下要加union 表示後面還有要串的資料   */
			if(i>count && i<lastIndex){
				sql.append(" union all ");
			}
		}

		sql.append(") a").append("\r\n");	

		sql.append("group by   accountno ,groupno,groupname    ").append("\r\n");	


		if(order){
			sql.append("order by  accountno ,groupno,groupname  ");
		}

		return sql.toString();
	}





}
